﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.IO;

namespace AllNations.Framework
{
    public sealed class ExcelHelper
    {
        public static void ExportToExcel(DataTable dt, string sFilename)
        {
            // Create a new workbook and worksheet.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
            worksheet.Name = "ANMC";

            // Get the top left cell for the DataTable.
            SpreadsheetGear.IRange range = worksheet.Cells["A1"];

            // Copy the DataTable to the worksheet range.
            range.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.AllText);

            // Auto size all worksheet columns which contain data
            worksheet.UsedRange.Columns.AutoFit();

            if (dt.Rows.Count > 0)
            {
                for (int idx = 0; idx < dt.Columns.Count; idx++)
                {
                    if (dt.Columns[idx].DataType != typeof(decimal))
                        worksheet.UsedRange.Cells[1, idx, worksheet.UsedRange.RowCount - 1, idx].NumberFormat = "@";
                }
            }

            // Stream the Excel spreadsheet to the client in a format
            // compatible with Excel 97/2000/XP/2003/2007.
            byte[] byteData = null;
            if (worksheet != null)
            {
                workbook.Worksheets[0].Select();
                byteData = workbook.SaveToMemory(SpreadsheetGear.FileFormat.XLS97);
            }

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("Content-Length", byteData.Length.ToString());
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", sFilename));
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.BinaryWrite(byteData);
            HttpContext.Current.Response.End();
        }

        public static DataSet ReadExcel(Stream stream)
        {
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.OpenFromStream(stream);
            return workbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.FormattedText);
        }

        public static DataSet ReadExcel(string sFilename)
        {
            // Create a workbook from an Excel file
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(sFilename);

            // Get a DataSet from an existing defined name
            return workbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.None);
        }
    }
}